use pamd;

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'searchListing' AND type = 'P')
   DROP PROCEDURE searchListing
GO

CREATE PROCEDURE searchListing
   @qText varchar(40),
   @PageNumber    int,
   @PageSize      int,
   @TotalCount	  int out
AS 

SELECT		
			IDENTITY(int, 1,1) AS rowNum,
			Listing.ID, 
			Listing.Title, 
			Listing.NumberOfSpace, 
			Listing.SpaceCategory, 
			Listing.SpaceRestriction, 
			Listing.IsPrivate, 
			Listing.SpaceDescription, 
			Listing.ContactNumber, 
			Address.Line1, 
			Address.Line2, 
			Address.Line3, 
			Address.City, 
			Address.County, 
			Address.PostCode, 
			Address.Country,
			ListingPhoto.FileName,
			Bid, 
			PriceDuration, 
			Vehicle, 
			Price,
			Available247,
			KeyReq,
			CCTV,
			Gated,
			LocalTrain,
			Lighted,
			LocalBus,
			Sheltered,
			SecurityGuard
INTO
#tmpPaged
FROM         
Address 
INNER JOIN Listing ON Address.ID = Listing.AddressID AND Listing.IsActive = 1
LEFT OUTER JOIN ListingPhoto ON Listing.ID = ListingPhoto.ListingID AND ListingPhoto.IsPrimary = 1
LEFT OUTER JOIN (select lp.ListingID, Price, PriceDuration, Vehicle, Bid from ListingPrice lp INNER JOIN (select min(id) id, ListingID from ListingPrice group by Listingid ) ld on lp.id = ld.id) p ON Listing.ID =   p.ListingID 
LEFT OUTER JOIN ListingFeature ON Listing.FeatureId = ListingFeature.id

                      WHERE  Address.Line1 + ' ' 
                      + Address.Line2 + ' ' 
                      + Address.Line3 + ' ' 
                      + Address.City + ' ' 
					  + Address.County LIKE  '%'+ @qText + '%'
                      OR 
                      Listing.Title + ' ' 
                      + Listing.SpaceDescription LIKE  '%'+ @qText + '%';
                      
   -- Create an index on rowNum
    CREATE UNIQUE CLUSTERED INDEX
      idx_rownum
    ON
      #tmpPaged(rowNum)   ;
     
     SELECT 
			ID, 
			Title, 
			NumberOfSpace, 
			SpaceCategory, 
			SpaceRestriction, 
			IsPrivate, 
			SpaceDescription, 
			ContactNumber, 
			Line1, 
			Line2, 
			Line3, 
			City, 
			County, 
			PostCode, 
			Country,
			FileName,
			Bid, 
			PriceDuration, 
			Vehicle, 
			Price,
			Available247,
			KeyReq,
			CCTV,
			Gated,
			LocalTrain,
			Lighted,
			LocalBus,
			Sheltered,
			SecurityGuard      
  FROM
      #tmpPaged      
    WHERE
      rowNum BETWEEN (@PageNumber-1)*@PageSize+1 AND @PageNumber*@PageSize;
 
    SET @TotalCount = (SELECT COUNT(*) FROM #tmpPaged);
    
GO